# coding: UTF-8
__author__ = 'Yaxin'

import os


#建立乘客和geohash的映射关系
cmd = "hive -e \"" \
      "add file /home/huyaxin/udf/buildgeohash.py;" \
      "use test;" \
      "drop table if exists test.pag_geohash1;" \
      "drop table if exists test.pag_geohash2;" \
      "drop table if exists test.pag_geohash;" \
      "create table test.pag_geohash1 as select transform(start_lat, start_lng, pag_mobile) " \
      "using 'python buildgeohash.py' as geohash, pag_mobile from vip.dw_jh_dd_ord_info " \
      "where ord_city_name in ('上海市', '北京市', '广州市', '深圳市', '杭州市', '成都市') " \
      "and pt between '2015-06-20' and '2015-09-20';" \
      "create table test.pag_geohash2 as select transform(dest_lat, dest_lng, pag_mobile) " \
      "using 'python buildgeohash.py' as geohash, pag_mobile from vip.dw_jh_dd_ord_info " \
      "where ord_city_name in ('上海市', '北京市', '广州市', '深圳市', '杭州市', '成都市') " \
      "and pt between '2015-06-20' and '2015-09-20';" \
      "create table test.pag_geohash as select pag_mobile, geohash, count(*) count from " \
      "(select pag_mobile, geohash from (select pag_mobile, geohash from pag_geohash1 union all " \
      "select pag_mobile, geohash from pag_geohash2) a where a.pag_mobile is not null) b group by " \
      "pag_mobile, geohash;\""
os.system(cmd)


#建立乘客和top5位置的房价的映射关系
cmd = "hive -e \"" \
      "add file /home/huyaxin/udf/communities.csv;" \
      "add file /home/huyaxin/udf/findprice.py;" \
      "use test;" \
      "drop table if exists test.pag_top_geo;" \
      "drop table if exists test.pag_top_price;" \
      "create table test.pag_top_geo as select pag_mobile, geohash, count from (select pag_mobile, geohash, count," \
      "row_number() over (distribute by pag_mobile sort by count desc) as rank from test.pag_geohash) a " \
      "where rank <= 5;" \
      "create table test.pag_top_price as select transform(pag_mobile, geohash) using 'python findprice.py' " \
      "as pag_mobile, geohash, price from test.pag_top_geo;\""
os.system(cmd)